﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using QD.Entity;
using QD.DataBase.DBHelper;
using QD.Data.Parameter;
using QD.Data.Page;
using QD.Data.Query;

namespace QD.Repository
{
    public delegate T BuildModelHandle<T>(ModelDataReader r);

    public abstract class RepositorySql
    {
        public TableSchema dtContext { get; protected set; }
        protected SQLHelper DBHelper { get; set; }
        public string ConnectionString { get; protected set; }

        protected string UpdateCommand;
        protected CommandType UpdateType;
        protected string SelectCommand;
        protected CommandType SelectType;
        protected string InsertCommand;
        protected CommandType InsertType;
        protected string DeleteCommand;
        protected CommandType DeleteType;
        protected string ExistCommand;
        protected CommandType ExistType;
        protected string PageListSQL;

        protected string CountCommand;
        protected CommandType CountType;

        protected IDBPramaBuilder ParamBuilder { get; set; }

        protected char QuoteLeft = '\0';
        protected char QuoteRight = '\0';
        protected string ParamPrev = "";

        protected virtual void Init()
        {
            int i, last = dtContext.Columns.Length - 1;


            #region Update

            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("UPDATE  " + QuoteLeft + "{0}" + QuoteRight + "  ", dtContext.Name);
            sql.Append("SET ");

            for (i = 0; i < dtContext.Columns.Length; ++i)
                if (!dtContext.Columns[i].AutoInCrement)
                    sql.AppendFormat(QuoteLeft + "{0}" + QuoteRight + " = " + ParamPrev + "{0}{1} ", dtContext.Columns[i].Name, i == last ? "" : ",");

            sql.AppendFormat(" WHERE  " + QuoteLeft + "{0}" + QuoteRight + " = " + ParamPrev + "{0};", dtContext.PrimaryKey.Name);

            UpdateCommand = sql.ToString();

            #endregion

            #region Insert

            sql = new StringBuilder();
            sql.AppendFormat("INSERT INTO  " + QuoteLeft + "{0}" + QuoteRight + "   ", dtContext.Name);
            sql.Append("(");
            if (!dtContext.PrimaryKey.AutoInCrement)
                sql.Append(QuoteLeft + dtContext.PrimaryKey.Name + QuoteRight + ",");
            for (i = 0; i < dtContext.Columns.Length; ++i)
            {
                if (!dtContext.Columns[i].AutoInCrement && !dtContext.Columns[i].IsPrimaryKey)
                    sql.AppendFormat(QuoteLeft + "{0}" + QuoteRight + ",", dtContext.Columns[i].Name);
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append(") VALUES(");
            if (!dtContext.PrimaryKey.AutoInCrement)
                sql.Append(ParamPrev + dtContext.PrimaryKey.Name + ",");
            for (i = 0; i < dtContext.Columns.Length; ++i)
            {
                if (!dtContext.Columns[i].AutoInCrement && !dtContext.Columns[i].IsPrimaryKey)
                    sql.AppendFormat(ParamPrev + "{0},", dtContext.Columns[i].Name);
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append(");");

            InsertCommand = sql.ToString();

            #endregion

            #region Delete

            DeleteCommand = String.Format("DELETE FROM " + QuoteLeft + "{0}" + QuoteRight + " WHERE " + QuoteLeft + "{1}" + QuoteRight + " = " + ParamPrev + "{1};", dtContext.Name, dtContext.PrimaryKey.Name);

            #endregion

            #region Exists

            ExistCommand = String.Format("select count(*) from " + QuoteLeft + "{0}" + QuoteRight + "  where " + QuoteLeft + "{1}" + QuoteRight + " = " + ParamPrev + "{1};", dtContext.Name, dtContext.PrimaryKey.Name);

            #endregion

            #region Selete

            SelectCommand = String.Format("select * from " + QuoteLeft + "{0}" + QuoteRight + "  where " + QuoteLeft + "{1}" + QuoteRight + " = " + ParamPrev + "{1};", dtContext.Name, dtContext.PrimaryKey.Name);

            #endregion

            CountCommand = "";
            InsertType = CommandType.Text;
            SelectType = CommandType.Text;
            UpdateType = CommandType.Text;
            DeleteType = CommandType.Text;
            ExistType = CommandType.Text;
            CountType = CommandType.Text;
        }

        protected IList<U> FindEntitys<U>(String connectionString, CommandType type, string commandtext, params IDbDataParameter[] parameters) where U : IDbEntity<U>, new()
        {
            List<U> list = new List<U>();
            IDataReader reader = null;
            U model = default(U);
            using (reader = DBHelper.ExecuteReader(connectionString, type, commandtext, parameters))
            {

                while (reader.Read())
                {
                    model = new U();
                    model.__init_from_db((ModelDataReader)(reader as DbDataReader));
                    list.Add(model);
                }
            }
            return list;
        }

        protected U GetEntity<U>(String connectionString, CommandType type, string commandtext, BuildModelHandle<U> handle, params IDbDataParameter[] parameters) where U : new()
        {
            U model = default(U);
            IDataReader reader = null;
            using (reader = DBHelper.ExecuteReader(connectionString, type, commandtext, parameters))
            {
                if (reader.Read())
                {
                    model = handle((ModelDataReader)(reader as DbDataReader));
                }
            }
            return model;
        }

        protected U GetEntity<U>(String connectionString, CommandType type, string commandtext, params IDbDataParameter[] parameters) where U : IDbEntity<U>, new()
        {
            U model = default(U);
            IDataReader reader = null;

            using (reader = DBHelper.ExecuteReader(connectionString, type, commandtext, parameters))
            {
                if (reader.Read())
                {
                    model = new U();
                    model.__init_from_db((ModelDataReader)(reader as DbDataReader));
                }
            }
            return model;
        }

        protected IList<U> GetEntitys<U>(String connectionString, CommandType type, string commandtext, params IDbDataParameter[] parameters) where U : IDbEntity<U>, new()
        {
            List<U> list = new List<U>();
            IDataReader reader = null;
            U model = default(U);
            using (IDbConnection conn = DBHelper.CreateConnection(connectionString))
            {
                foreach (IDbDataParameter item in parameters)
                {
                    reader = DBHelper.ExecuteReader(connectionString, type, commandtext, item);
                    if (reader.Read())
                    {
                        model = new U();
                        model.__init_from_db((ModelDataReader)(reader as DbDataReader));
                        list.Add(model);
                    }
                }
            }
            return list;
        }

        public void ExecuteTransaction(IsolationLevel level, TransationHandle handle)
        {
            DBHelper.ExecuteTransation(ConnectionString, level, handle);
        }

    }
    public delegate Int32 SaveDataCounterHandle<T>(IDbConnection conn, T entity);

    public abstract class RepositorySql<T> : RepositorySql, IRepository<T> where T : DbEntity, IDbEntity<T>, new()
    {
        #region Parameters

        protected virtual IDbDataParameter[] BuildAllParams(T model) { IDbDataParameter[] parameters = BuildEmptyParams(); PrepareParams(model, parameters); return parameters; }

        protected virtual IDbDataParameter[] BuildEmptyParams()
        {
            List<IDbDataParameter> parameters = new List<IDbDataParameter>();
            foreach (ColumnSchema item in dtContext.Columns)
                parameters.Add(ParamBuilder.Create(ParamPrev + item.Name));
            return parameters.ToArray();
        }

        protected virtual void PrepareParams(T model, params IDbDataParameter[] parameters)
        {
            int i = 0;
            for (; i < dtContext.Columns.Length; i++)
                parameters[i].Value = model.__get_data(i) ?? DBNull.Value;
        }

        #endregion

        #region IRepository

        #region Insert

        public virtual int Create(params T[] entities)
        {
            IDbDataParameter[] paramters = BuildEmptyParams();
            int ret = 0;
            using (IDbConnection conn = DBHelper.CreateConnection(ConnectionString))
            {
                conn.Open();

                foreach (T item in entities)
                {
                    PrepareParams(item, paramters);
                    if (dtContext.PrimaryKey.AutoInCrement)
                        paramters[item.__get_primary_idx()].Direction = ParameterDirection.Output;
                    ret += DBHelper.ExecuteNonQuery(conn, InsertType, InsertCommand, paramters);
                    if (paramters[item.__get_primary_idx()].Direction == ParameterDirection.Output || paramters[item.__get_primary_idx()].Direction == ParameterDirection.InputOutput && paramters[item.__get_primary_idx()].Value != DBNull.Value)
                        item.__set_primary_key(paramters[item.__get_primary_idx()].Value);
                }
            }


            return ret;
        }

        #endregion

        #region Update

        public virtual int Update(params T[] entities)
        {
            IDbDataParameter[] paramters = BuildEmptyParams();
            int ret = 0;
            using (IDbConnection conn = DBHelper.CreateConnection(ConnectionString))
            {
                conn.Open();
                foreach (T item in entities)
                {
                    PrepareParams(item, paramters);
                    ret += DBHelper.ExecuteNonQuery(conn, UpdateType, UpdateCommand, paramters);
                }
            }
            return ret;
        }

        #endregion

        #region Delete

        public virtual int Delete(params object[] pk)
        {
            IDbDataParameter paramters = ParamBuilder.Create(String.Format(ParamPrev + "{0}", dtContext.PrimaryKey.Name));
            int ret = 0;
            using (IDbConnection conn = DBHelper.CreateConnection(ConnectionString))
            {

                conn.Open();
                foreach (object item in pk)
                {
                    paramters.Value = item;
                    ret += DBHelper.ExecuteNonQuery(conn, DeleteType, DeleteCommand, paramters);
                }

            }
            return ret;
        }

        public virtual int Delete(Query query)
        {
            if (query == null) return 0;
            String sql = "DELETE FROM " + QuoteLeft + dtContext.Name + QuoteRight + " WHERE " + query.Expression;
            return DBHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql);
        }

        #endregion

        #region Exists

        public virtual bool Exists(object pk)
        {
            object ret = 0;
            IDbDataParameter[] parameters = { 
                            ParamBuilder.Create(String.Format(ParamPrev+"{0}",dtContext.PrimaryKey.Name),pk)
                       };
            ret = DBHelper.ExecuteScalar(ConnectionString, ExistType, ExistCommand, parameters);
            return Convert.ToInt32(ret) > 0;
        }

        #endregion

        #region Count

        public virtual int Count(Query query)
        {
            return Count(query == null ? "" : query.Expression);
        }

        public virtual int Count(string where)
        {
            object ret = null;
            String sql = null;
            if (!String.IsNullOrEmpty(where))
                where = "where " + where;
            if (CountType == CommandType.Text)
            {
                StringBuilder sqlRaw = new StringBuilder();
                sqlRaw.AppendFormat("select count(*) from " + QuoteLeft + "{0}" + QuoteRight + " {1}", dtContext.Name, where);
                sql = sqlRaw.ToString();
            }
            else
                sql = CountCommand;
            ret = DBHelper.ExecuteScalar(ConnectionString, CountType, sql);
            return ret == null ? 0 : Convert.ToInt32(ret);
        }

        #endregion

        #region  GetModel

        public virtual T Get(object pk)
        {
            T model = default(T);
            IDbDataParameter parameter = ParamBuilder.Create(String.Format(ParamPrev + "{0}", dtContext.PrimaryKey.Name), pk);
            model = GetEntity<T>(ConnectionString, SelectType, SelectCommand, parameter);
            return model;
        }

        public virtual U Get<U>(object pk) where U : IDbEntity<U>, new()
        {
            U model = default(U);
            IDbDataParameter parameter = ParamBuilder.Create(String.Format(ParamPrev + "{0}", dtContext.PrimaryKey.Name), pk);
            model = GetEntity<U>(ConnectionString, SelectType, SelectCommand, parameter);

            return model;
        }

        #endregion

        #region PageList

        public virtual PageList<T> Find(int page, int pagesize)
        {
            return Find(page, pagesize, null);
        }

        public virtual PageList<T> Find(int page, int pagesize, Query query)
        {
            string where = String.Empty, order = String.Empty;
            if (query != null)
            {
                where = query.Expression;
                order = query.Order;

            }
            return Find(page, pagesize, where, order);
        }

        public virtual PageList<T> Find(int page, int pagesize, string where, string order)
        {
            return Find<T>(page, pagesize, where, order);
        }
       
        #endregion

        #region Find

        public virtual IList<T> Find()
        {
            return Find(1, Int16.MaxValue, null).Records;
        }

        public virtual IList<T> Find(Query query)
        {
            return Find(1, Int16.MaxValue, query).Records;
        }

        #endregion

        #region Find<T>

        #region PageList

        public virtual PageList<U> Find<U>(Int32 page, Int32 pagesize) where U : DbEntity, IDbEntity<U>, new()
        {

            return Find<U>(page, pagesize, null);
        }

        public virtual PageList<U> Find<U>(Int32 page, Int32 pagesize, Query query) where U : DbEntity, IDbEntity<U>, new()
        {
            string where = String.Empty, order = String.Empty;
            if (query != null)
            {
                where = query.Expression;
                order = query.Order;
            }
            return Find<U>(page, pagesize, where, order);
        }

        public abstract PageList<U> Find<U>(int page, int pagesize, string where, string order) where U : DbEntity, IDbEntity<U>, new();

       

        #endregion

        #region

        public virtual IList<U> Find<U>() where U : DbEntity, IDbEntity<U>, new()
        {
            return Find<U>(1, Int16.MaxValue, null).Records;
        }

        public virtual IList<U> Find<U>(Query query) where U : DbEntity, IDbEntity<U>, new()
        {
            return Find<U>(1, Int16.MaxValue, query).Records;
        }

        #endregion

        #endregion

        #endregion

    }
}
